from myApp.model.build_model import BuildInfo
from myApp.utils.ChatGlmModel import run_glm_all, run_glm

# 配置数据库连接URI等
# 原始密码
# password = "vsoft@123&"
#
# # URL 编码密码
# encoded_password = quote(password)
# app.config['SQLALCHEMY_DATABASE_URI'] = f"mysql+pymysql://root:{encoded_password}@127.0.0.1/chatweb"
#
# # app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://chatweb:chatweb@47.94.18.5/chatweb'
# app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 避免警告
# # 初始化数据库连接
# db.init_app(app)

task_1_prompt = """
阅读下面描述，将其转为SQL语句，识别犯罪类型的关键词作为检索条件，对字段AJMC进行模糊查询，如果有多个关键词请用or进行连接。

[@用户输入@]

[示例SQL]
sql
Copy code
SELECT 
    c.bmsah AS '案件编号', 
    c.ajmc AS '案件名称', 
    c.slrq AS '受理日期', 
    c.cbdw AS '承办单位代码', 
    b.xm AS '嫌疑人姓名', 
    b.zjhm AS '证件号码'
FROM 
    t_tyyw_xj_ysgs_aj c
LEFT JOIN 
    t_tyyw_xj_ysgs_xyr b ON b.bmsah = c.bmsah
WHERE 
    c.ajmc LIKE '%[这里填写关键词1]%'
    or c.ajmc LIKE '%[这里填写关键词2]%'
ORDER BY 
    c.cbdw
注意，除了SQL语句外不需要其他内容。


"""

import mysql.connector
import json


def build_model(text, assistant_message_id):
    # Assuming task_1_prompt is a predefined SQL query template
    sql_query = run_glm_all(task_1_prompt.replace("@用户输入@", text), assistant_message_id)
    # Connect to your MySQL database
    connection = mysql.connector.connect(

        host='127.0.0.1',
        user='root',
        password='vsoft@123&',
        database='chatweb'  # Specify your database name here
    )
    cursor = connection.cursor()

    paginated_sql_str = f"{sql_query.replace(';', '')} LIMIT 10 OFFSET 0"
    # Execute the SQL query
    cursor.execute(paginated_sql_str)


    # Fetching the headers
    headers = [description[0] for description in cursor.description]
    yield json.dumps({
        'text': f"{'| ' + ' | '.join(headers) + ' |'}\n"}) + "&&##&&"
    yield json.dumps({
        'text': f"{'| ' + ' | '.join(['---'] * len(headers)) + ' |'}\n"}) + "&&##&&"

    # Fetching and yielding each row of results
    for row in cursor.fetchall():
        yield json.dumps({
            'text': f"{'| ' + ' | '.join(str(item) for item in row) + ' |'}\n"}) + "&&##&&"

    yield sql_query
    from app import app

    with app.app_context():
        id = BuildInfo.create_build(sql_query, text)
        yield json.dumps({
            'text': f"\n\n[更多信息 >](searchBuild?id={id})\n"}) + "&&##&&"
    # Close the connection
    cursor.close()
    connection.close()
